﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Diagnostics;



namespace WindowsFormsApplication3.权限管理
{  
    public partial class permissionManaging : Form
    {
        public static string addStudentsInfo_ID, addTeachersInfo_ID;
        public string dataTableName;
        public static SQL_Connection mySql = new SQL_Connection("server=.;database=Intelligent_storage_system;uid=sa;pwd=ncl5722354");
        public permissionManaging()
        {
            InitializeComponent();
            
        }

        private void permissionManaging_Load(object sender, EventArgs e)
        {
            //创建数据库所有的表
            
            // mySql.excute_sql("CREATE table usertable(username nvarchar(50) PRIMARY KEY,passwd nvarchar(50),pression nvarchar(50))"); //用户表
            mySql.excute_sql("CREATE table studentstable(studentsID nvarchar(50) PRIMARY KEY,username nvarchar(50) ,passwd nvarchar(50),pression nvarchar(50),departments nvarchar(50),classes nvarchar(50))"); //用户表
            mySql.excute_sql("CREATE table teacherstable(teachersID nvarchar(50) PRIMARY KEY,username nvarchar(50) ,passwd nvarchar(50),pression nvarchar(50),departments nvarchar(50))"); //教师表
            mySql.excute_sql("CREATE table sqlTableName_and_IDtable(userID nvarchar(50) PRIMARY KEY,tableName nvarchar(50))");
            //Read_DB_Fill_Grid("usertalbe", dataGridView1);
            

        }
        //填充用户权限管理界面
        private void Read_DB_Fill_Grid(string table_name, DataGridView datagriview1)
        {
            // 用数据库将表格填满
            try
            {
                DataSet ds;
                if (table_name == "studentstable")
                {
                    ds = 权限管理.permissionManaging.mySql.sql_search_database("select studentsID,username,passwd,pression from " + table_name);
                }
                else
                {
                    ds = 权限管理.permissionManaging.mySql.sql_search_database("select teachersID,username,passwd,pression from " + table_name);
                }
                int colnum = ds.Tables[0].Columns.Count;         // 列数
                dataGridView1.ColumnCount = colnum;              // 设置列数
                int rownum = ds.Tables[0].Rows.Count;            // 行数
                dataGridView1.RowCount = rownum;                 // 设置行数
                for (int i = 0; i < rownum; i++)
                {
                    for (int j = 0; j < colnum; j++)
                    {
                        dataGridView1[j, i].Value = ds.Tables[0].Rows[i][j].ToString();     // 把那一行显示出来
                    }
                }
            }
            catch
            {
                for (int i = 0; i < dataGridView1.RowCount; i++)
                {
                    for (int j = 0; j < dataGridView1.ColumnCount; j++)
                    {
                        dataGridView1[j, i].Value = "";     // 把那一行显示出来
                    }
                }
            } 
        }

        //添加用户
        private void Button_permissions_add_Click(object sender, EventArgs e)
        {
            try
            {            
                权限管理.add_info myAdd_info = new 权限管理.add_info();
                myAdd_info.ShowDialog();
                if (addStudentsInfo_ID != null)
                {
                    //搜索数据
                    DataSet ds = mySql.sql_search_database("select studentsID,username,passwd,pression from studentstable  where studentsID = '"
                        + addStudentsInfo_ID + "' ");                   
                    // 填充数据
                    dataGridView1.Rows.Clear();
                    for (int i = 0; i < 4; i++) dataGridView1[i, 0].Value = ds.Tables[0].Rows[0][i].ToString();                  
                    //清除全局变量
                    addStudentsInfo_ID = null;
                }
                else
                {
                    //搜索数据
                    DataSet ds = mySql.sql_search_database("select teachersID,username,passwd,pression from teacherstable  where teachersID = '"
                        + addTeachersInfo_ID + "' ");                   
                    //填充数据 
                    dataGridView1.Rows.Clear();
                    for (int i = 0; i < 4; i++) dataGridView1[i, 0].Value = ds.Tables[0].Rows[0][i].ToString();                   
                    //清除全局变量
                    addTeachersInfo_ID = null;
                }             
            }
            catch
            { 
            
            }
        }
        // 此处为班级编号文本处的回车按键时间  暂时为处理  单击后出发数据库查询
        private void textBox1_KeyDown(object sender, KeyEventArgs e)
        {
            
        }
        //学生搜索
        private void search_Click(object sender, EventArgs e)
        {
            dataTableName = "studentstable"; //监测dataGridView1处于的表
            string temp = "";

            if (departments.Text != "")
            {
                temp += "departments = '" + departments.Text + "' ";
                if (classes.Text != "") temp += " and ";
                else temp += " or ";
            }
            else temp += "departments = '" + "'" + " or ";   
            if (classes.Text != "")
            {
                temp += "classes = '" + classes.Text + "' ";
                if (studentsNum.Text != "") temp += " and ";
                else temp += " or ";
            }
            else temp += "classes = '" + "' " + " or ";
            if (studentsNum.Text != "") temp += "studentsID = '" + studentsNum.Text + "'";
            else                        temp += "studentsID = '" + "'";
            
                
            

            try
            {
                // 学生表
                DataSet ds = mySql.sql_search_database("select studentsID,username,passwd,pression from studentstable  where " + temp);           
                int column = ds.Tables[0].Columns.Count;//列数
                dataGridView1.ColumnCount = column;
                int row = ds.Tables[0].Rows.Count;// 行数
                dataGridView1.RowCount = row;
                for (int i = 0; i < row; i++)
                {
                    for (int j = 0; j < column; j++)
                    {
                        dataGridView1[j, i].Value = ds.Tables[0].Rows[i][j].ToString();
                    }
                }
            }
            catch
            {
                for (int i = 0; i < dataGridView1.RowCount; i++)
                {
                    for (int j = 0; j < dataGridView1.ColumnCount; j++)
                    {
                        dataGridView1[j, i].Value = "";     // 把那一行显示出来
                    }
                }
            }    
        }

        //教师搜索
        private void search1_Click(object sender, EventArgs e)
        {
            dataTableName = "teacherstable";
            string temp = "";

            if (departments1.Text != "")
            {
                temp += "departments = '" + departments1.Text + "' ";
                if (teachers.Text != "") temp += " and ";
                else temp += " or ";
            }
            else
            {
                temp += "departments = '" + "' " + " or ";
            }
            if (teachers.Text != "")
            {
                temp += "username = '" + teachers.Text + "' ";
                if (jobNum.Text != "") temp += " and ";
                else temp += " or ";
            }
            else
            {
                temp += "username = '" + "' " + " or ";
            }
            if (jobNum.Text != "")
            {
                temp += "teachersID = '" + jobNum.Text + "'";
            }
            else
            {
                temp += "teachersID = '" + "'";
            }
 
            try
            {
                // 教师表
                DataSet ds = mySql.sql_search_database("select teachersID,username,passwd,pression from teacherstable  where " + temp);
                //Debug.WriteLine("select teachersID,username,passwd,pression from teacherstable  where " + temp);
                  
                int column = ds.Tables[0].Columns.Count;//列数
                dataGridView1.ColumnCount = column;
                int row = ds.Tables[0].Rows.Count;// 行数
                dataGridView1.RowCount = row;
                for (int i = 0; i < row; i++)
                {
                    for (int j = 0; j < column; j++)
                    {
                        dataGridView1[j, i].Value = ds.Tables[0].Rows[i][j].ToString();
                    }
                }
            }
            catch
            {
                for (int i = 0; i < dataGridView1.RowCount; i++)
                {
                    for (int j = 0; j < dataGridView1.ColumnCount; j++)
                    {
                        dataGridView1[j, i].Value = "";     // 把那一行显示出来
                    }
                }
            }
        }

        //“更改”按键
        private void button_permissions_change_Click(object sender, EventArgs e)
        {
            try
            {
                //确定使用者使用的哪张数据表
                string userID, userTable;
                int selected_index = dataGridView1.SelectedCells[0].RowIndex;
                userID = dataGridView1[0, selected_index].Value.ToString(); //当前使用的是哪个用户：学生 or 教师
                DataSet dsSearchTable = mySql.sql_search_database("select tableName from sqlTableName_and_IDtable  where userID = '"
                    + userID + "'");
                //Debug.WriteLine("select tableName from sqlTableName_and_IDtable  where userID = '"
                 //   + userID + "'");
                userTable = dsSearchTable.Tables[0].Rows[0][0].ToString();


                if (userTable == "studentstable")
                {
                    string myStudentsDepartments, myStudentsClasses;
                    string myStudentsID = dataGridView1[0, selected_index].Value.ToString();// 学生ID
                    string myStudentsName = dataGridView1[1, selected_index].Value.ToString();// 学生姓名
                    string myStudentsPasswd = dataGridView1[2, selected_index].Value.ToString();//学生密码
                    string myStudentsPressions = dataGridView1[3, selected_index].Value.ToString();//学生权限

                    DataSet ds = mySql.sql_search_database("select departments,classes from studentstable  where studentsID = '"
                         + myStudentsID + "'");
                    Debug.WriteLine("select departments,classes from studentstable  where studentsID = '" + myStudentsID + "'");      
                    myStudentsDepartments = ds.Tables[0].Rows[0][0].ToString();
                    myStudentsClasses = ds.Tables[0].Rows[0][1].ToString();

                    // 灌进editStudents窗体中
                    权限管理.editStudents myEditStudents = new 权限管理.editStudents();
                    myEditStudents.myEdit_ID = myStudentsID;
                    myEditStudents.myEdit_name = myStudentsName;
                    myEditStudents.myEdit_passwd = myStudentsPasswd;
                    myEditStudents.myEdit_pressions = myStudentsPressions;
                    myEditStudents.myEdit_departments = myStudentsDepartments;
                    myEditStudents.myEdit_classes = myStudentsClasses;
                    myEditStudents.fill_Info();  //填充到editStudents窗体中
                    //myEditStudents.del_Info();   //删除表中该条信息
                    myEditStudents.ShowDialog();
                    Read_DB_Fill_Grid("studentstable", dataGridView1);
                }
                else
                {
                    string myTeachersDepartments;
                    string myTeachersID = dataGridView1[0, selected_index].Value.ToString();// 教师ID
                    string myTeachersName = dataGridView1[1, selected_index].Value.ToString();// 教师姓名
                    string myTeachersPasswd = dataGridView1[2, selected_index].Value.ToString();//教师密码
                    string myTeachersPressions = dataGridView1[3, selected_index].Value.ToString();//教师权限
                    DataSet ds = mySql.sql_search_database("select departments from teacherstable  where teachersID = '"
                        + myTeachersID + "'");
                    myTeachersDepartments = ds.Tables[0].Rows[0][0].ToString();
                    // 灌进editTeachers窗体中
                    权限管理.editTeachers myEditTeachers = new 权限管理.editTeachers();
                    myEditTeachers.myEdit_ID = myTeachersID;
                    myEditTeachers.myEdit_name = myTeachersName;
                    myEditTeachers.myEdit_passwd = myTeachersPasswd;
                    myEditTeachers.myEdit_pressions = myTeachersPressions;
                    myEditTeachers.myEdit_departments = myTeachersDepartments;
                    myEditTeachers.fill_Info();  //填充到editTeachers窗体中
                    //myEditTeachers.del_Info();   //删除表中该条信息
                    myEditTeachers.ShowDialog();
                    Read_DB_Fill_Grid("teacherstable", dataGridView1);
                }        
            }
            catch
            { 
            
            }
        }

        private void button_permissions_delete_Click(object sender, EventArgs e)
        {
            try
            {
                //确定使用者使用的哪张数据表
                string userID, userTable;
                int selected_index = dataGridView1.SelectedCells[0].RowIndex;
                userID = dataGridView1[0, selected_index].Value.ToString(); //当前使用的是哪个用户：学生 or 教师
                DataSet dsSearchTable = mySql.sql_search_database("select tableName from sqlTableName_and_IDtable  where userID = '"
                    + userID + "'");
                Debug.WriteLine("select tableName from sqlTableName_and_IDtable  where userID = '"
                    + userID + "'");
                userTable = dsSearchTable.Tables[0].Rows[0][0].ToString();


                if (userTable == "studentstable")
                {
                    权限管理.delStudents myDelStudents = new 权限管理.delStudents();
                    myDelStudents.del_info = userID;// 学生ID
                    myDelStudents.ShowDialog();
                    Read_DB_Fill_Grid("studentstable", dataGridView1);
                }
                else
                {
                    权限管理.delTeachers myDelTeachers = new 权限管理.delTeachers();
                    myDelTeachers.del_info = userID;
                    myDelTeachers.ShowDialog();
                    Read_DB_Fill_Grid("teacherstable", dataGridView1);
                }
               

            }
            catch
            { 
                
            }

        }  
    }
}
